package com.iweb.dao;

import com.iweb.db.XdDataSource;
import com.iweb.pojo.Commodity;
import com.iweb.pojo.CommodityPic;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CommodityDao {


    public Commodity findById(String id) {
        // TO DO

        return null;
    }

    public List<CommodityPic> findCommodityPics(String commodityId, String state) {
        // TO DO
        return null;
    }

    // 查询数据的总数，
    public long findCount() {
        Connection conn = XdDataSource.getConnection();
        String sql = "select count(1) from xd_commodity";
        int count = 0;
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return count;
    }

    // 分页查询
    public List<Commodity> findByPage(long current, long size) {
        List<Commodity> commodities = null;
        Connection conn = XdDataSource.getConnection();
        try {
            // 开始查询
            String sql = "select * from \n" +
                    "(select a.*,rownum rn from \n" +
                    "(select * from xd_commodity order by create_time) a where rownum <= ? ) b " +
                    "where b.rn > ?";
            // sql语句的预编译
            PreparedStatement pst = conn.prepareStatement(sql);
            // 对参数的结构进行转换  current size => startIndex endIndex
            long startIndex = (current - 1) * size;
            long endIndex = current * size;
            // 设置参数
            pst.setLong(1, endIndex);
            pst.setLong(2, startIndex);

            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                // 对list进行封装
                if (commodities == null) commodities = new ArrayList<>();
                Commodity commodity = createCommodity(rs);
                commodities.add(commodity);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return commodities;
    }

    private Commodity createCommodity(ResultSet rs) throws SQLException {
        Commodity commodity = new Commodity();
        return commodity.set(
                rs.getString(1),
                rs.getString(2),
                rs.getString(3),
                rs.getDouble(4),
                rs.getString(5),
                rs.getString(6),
                rs.getLong(7),
                rs.getTimestamp(8)
        );
    }
}
